Skip Headers

Oracle® Database Data Cartridge Developer's Guide, 10g Release 2 (10.2) Part Number B14289-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 3 Defining Object Types

This chapter provides an example of starting with a schema for a data cartridge. Object types are crucial to building data cartridges in that they enable domain-level abstractions to be captured in the database.

This chapter contains these topics:

- Objects and Object Types

- Assigning an Object Identifier to an Object Type

- Constructor Methods

- Object Comparison

  See Also: The following manuals for additional information about creating and using object types: Oracle Database Application Developer's Guide - Object-Relational Features Oracle Database Concepts Oracle Database Application Developer's Guide - Fundamentals PL/SQL User's Guide and Reference

## Objects and Object Types

In the Oracle Object-Relational Database Management System (ORDBMS), you use object types to model real-world entities. An object type has attributes, which reflect the entity's structure, and methods, which implement the operations on the entity. Attributes are defined using built-in types or other object types. Methods are functions or procedures written in PL/SQL or an external language like C and stored in the database.

A typical use for an object type is to impose structure on some part of the data kept in the database. For example, an object type named `DataStream` could be used by a cartridge to store large amounts of data in a character LOB (a data type for large objects). This object type has attributes such as an identifier, a name, a date, and so on. The following statement defines the `DataStream` datatype:

    create or replace type DataStream as object (
       id integer,
       name varchar2(20),
       createdOn date,
       data clob,
       MEMBER FUNCTION DataStreamMin  return pls_integer,
       MEMBER FUNCTION DataStreamMax  return pls_integer,
       MAP MEMBER FUNCTION DataStreamToInt  return integer,
       PRAGMA restrict_references(DataStreamMin, WNDS, WNPS),
       PRAGMA restrict_references(DataStreamMax, WNDS, WNPS));

A method is a procedure or function that is part of the object type definition and that can operate on the object type data attributes. Such methods are called member methods, and they take the keyword `MEMBER` when you specify them as a component of the object type. The `DataStream` type definition declares three methods. The first two, `DataStreamMin` and `DataStreamMax`, calculate the minimum and maximum values, respectively, in the data stream stored inside the character `LOB`.

The third method (`DataStreamToInt`), a map method, governs comparisons between instances of data stream type.

See Also: "Object Comparison" for information about map methods

The pragma (compiler directive) `RESTRICT_REFERENCES` is necessary for security, and is discussed in the following sections.

After declaring the type, define the type body. The body contains the code for type methods. The following example shows the type body definition for the `DataStream` type. It defines the member function methods (`DataStreamMin` and `DataStreamMax`) and the map method (`DataStreamToInt`).

    CREATE OR REPLACE TYPE BODY DataStream IS
        MEMBER FUNCTION DataStreamMin return pls_integer is
          a pls_integer := DS_Package.ds_findmin(data);
          begin return a; end;
        MEMBER FUNCTION DataStreamMax return pls_integer is
          b pls_integer := DS_Package.ds_findmax(data);
          begin return b; end;
        MAP MEMBER FUNCTION DataStreamToInt return integer is
          c integer := id;
          begin return c; end;
    end;

`DataStreamMin` and `DataStreamMax` are call routines in a PL/SQL package named `DS_Package`. Since these methods are likely to be compute-intensive (they process numbers stored in the `CLOB` to determine minimum and maximum values), they are defined as external procedures and implemented in C. The external dispatch is routed through a PL/SQL package named `DS_Package`. Such packages are discussed in PL/SQL Packages and Types Reference.

The third method (`DataStreamToInt`), the map method, is implemented in PL/SQL. Because we have a identifier (`id`) attribute in `DataStream`, this method can return the value of the identifier attribute. Most map methods, however, are more complex than `DataStreamToInt`.

See Also: Chapter 6, "Working with Multimedia Datatypes" for information about using LOBs with data cartridges Oracle Database Application Developer's Guide - Large Objects for general information about LOBs

## Assigning an Object Identifier to an Object Type

The `CREATE` `TYPE` statement has an optional keyword `OID`, which associates a user-specified object identifier (`OID`) with the type definition. It should be used by anyone who creates an object type that will be used in more than one database.

Each type has an `OID`. If you create an object type and do not specify an `OID`, Oracle generates an `OID` and assigns it to the type. Oracle uses the `OID` internally for operations pertaining to that type. Using the same `OID` for a type is important if you plan to share instances of the type across databases for such operations as export/import and distributed queries.

Note: Oracle documentation often refers to the use of `OIDs` with rows in object tables. In `CREATE` `TYPE` with `OID`, an `OID` is assigned to the type itself. Of course, each row created in a table with a column of the specified type will also still have a row-specific `OID`.

For example, assume that you want to create a type named `SpecialPerson` and then instantiate that type in two different databases with tables named `SpecialPersonTable1` and `SpecialPersonTable2`. The RDBMS needs to know that the `SpecialPerson` type is the same type in both instances, and therefore the type must be defined using the same `OID` in both databases. If you do not specify an `OID` with `CREATE` `TYPE`, a unique identifier is created automatically by the RDBMS.

The syntax for specifying an `OID` for an object type is:

    CREATE OR REPLACE TYPE type_name OID 'oid' AS OBJECT (attribute datatype [,...]);

In the following example, the `SELECT` statement generates an `OID`, and the `CREATE` `TYPE` statement uses the `OID` in creating an object type named `mytype`. Be sure to use the `SELECT` statement to generate a different `OID` for each object type to be created, because this is the only way to guarantee that each `OID` is valid and globally unique.

    SQLPLUS> SELECT SYS_OP_GUID() FROM DUAL;
    SYS_OP_GUID()
    --------------------------------
    19A57209ECB73F91E03400400B40BBE3
    1 row selected.

    SQLPLUS> CREATE TYPE mytype OID '19A57209ECB73F91E03400400B40BBE3'
         2> AS OBJECT (attrib1 NUMBER);
    Statement processed.

## Constructor Methods

Oracle implicitly defines a constructor method for each object type that you define. The name of the constructor method is the same as the name of the object type. The parameters of the constructor method are exactly the data attributes of the object type, and they occur in the same order as the attribute definition for the object type. Only one constructor method can be defined for each object type.

For example, when the system executes the following statement to create a type named `rational_type`, it also implicitly creates a constructor method for this object type.

    CREATE TYPE rational_type (
         numerator integer,
         denominator integer);

When you instantiate an object of `rational_type`, you invoke the constructor method. For example:

    CREATE TABLE some_table (
         c1 integer, c2 rational_type);
    INSERT INTO some_table
         VALUES (42, rational_type(223, 71));

## Object Comparison

SQL performs comparison operations on objects. Some comparisons are explicit, using the comparison operators (=, <, >, <>, <=, >=, !=) and the `BETWEEN` and `IN` predicates. Other comparisons are implicit, as in the `GROUP` `BY`, `ORDER` `BY`, `DISTINCT`, and `UNIQUE` clauses.

Comparison of objects makes use of special member functions of the object type: map methods and order methods. To perform object comparison, you must implement either a map method or order method in the `CREATE` `TYPE` and `CREATE` `TYPE` `BODY` statements.

For example, the type body for the `DataStream` type, implements the map member function for `DataStream` comparison as:

    MAP MEMBER FUNCTION DataStreamToInt return integer is
          c integer := id;
          begin return c; end;

This definition of the map member function relies on the presence of the `id` attribute of the `DataStream` type to map instances to integers. Whenever a comparison operation is required between objects of type `DataStream`, the map function `DataStreamToInt()` is called implicitly by the system.

The object type `rational_type` does not have a simple `id` attribute like that for `DataStream`. For `rational_type`, the map member function is slightly more complicated. Because a map function can return any of the built-in types, `rational_type` can return a value or type `REAL`:

    MAP MEMBER FUNCTION RationalToReal RETURN REAL IS
         BEGIN
             RETURN numerator/denominator;
         END;
    ...

If you have not defined a map or order function for an object type, only equality comparisons are allowed on objects of that type. Oracle SQL performs the comparison by doing a field-by-field comparison of the attributes of that type.